      1  import java.sql.Connection;

      2  import java.sql.PreparedStatement;

      3  import java.sql.ResultSet;

      4  import java.sql.SQLException;

      5  import java.sql.Statement;

      6  import java.io.IOException;

      7  import java.io.File;

      8  import java.util.Scanner;

      9

     10  /**

     11     Enters an invoice into the database.

     12     Be sure to add Customer.sql, Product.sql, Invoice.sql, and LineItem.sql

     13     to the database before running this program.

     14  */

     15  public class InvoiceEntry

     16  {

     17     public static void main(String args[])

     18     {

     19        if (args.length == 0)

     20        {

     21           System.out.println(

     22                 "Usage: java -classpath driver_class_path"

     23                 + File.pathSeparator

     24                 + ". InvoiceEntry propertiesFile");

     25           return;

     26        }

     27

     28        try

     29        {

     30           SimpleDataSource.init(args[0]);

     31           Connection conn = SimpleDataSource.getConnection();

     32           Scanner in = new Scanner(System.in);

     33

     34           addInvoice(in, conn);

     35        }

     36        catch (SQLException ex)

     37        {

     38           System.out.println("Database error");

     39           ex.printStackTrace();

     40        }

     41        catch (ClassNotFoundException ex)

     42        {

     43           System.out.println("Error loading database driver");

     44           ex.printStackTrace();

     45        }

     46        catch (IOException ex)

     47        {

     48           System.out.println("Error loading database properties");

     49           ex.printStackTrace();

     50        }

     51     }

     52

     53     public static void addInvoice(Scanner in, Connection conn)

     54        throws SQLException

     55     {

     56        try

     57        {

     58           int customerNumber = newCustomer(conn, in);

     59

     60           int id = getNewId(conn, "Invoice");

     61           PreparedStatement stat = conn.prepareStatement(

     62              "INSERT INTO Invoice VALUES (?, ?, 0)");

     63           stat.setInt(1, id);

     64           stat.setInt(2, customerNumber);

     65           stat.executeUpdate();

     66           stat.close();

     67

     68           boolean done = false;

     69           while (!done)

     70           {

     71              String productCode = nextLine(in, "Product code (D=Done, L=List)");

     72              if (productCode.equals("D")) { done = true; }

     73              else if (productCode.equals("L")) { listProducts(conn); }

     74              else if (findProduct(conn, productCode))

     75              {

     76                 int quantity = nextInt(in, "Quantity");

     77                 addLineItem(conn, id, productCode, quantity);

     78              }

     79              else { System.out.println("Invalid product code."); }

     80           }

     81           showInvoice(conn, id);

     82        }

     83        finally

     84        {

     85           conn.close();

     86        }

     87     }

     88

     89     /**

     90        Prompts the user for the customer information and creates a new customer.

     91        @param conn the database connection

     92        @param in the scanner

     93        @return the ID of the new customer

     94     */

     95     private static int newCustomer(Connection conn, Scanner in)

     96        throws SQLException

     97     {

     98        String name = nextLine(in, "Name");

     99        String address = nextLine(in, "Street address");

    100        String city = nextLine(in, "City");

    101        String state = nextLine(in, "State");

    102        String zip = nextLine(in, "Zip");

    103        int id = getNewId(conn, "Customer");

    104        PreparedStatement stat = conn.prepareStatement(

    105           "INSERT INTO Customer VALUES (?, ?, ?, ?, ?, ?)");

    106        stat.setInt(1, id);

    107        stat.setString(2, name);

    108        stat.setString(3, address);

    109        stat.setString(4, city);

    110        stat.setString(5, state);

    111        stat.setString(6, zip);

    112        stat.executeUpdate();

    113        stat.close();

    114        return id;

    115     }

    116

    117     /**

    118        Finds a product in the database.

    119        @param conn the database connection

    120        @param code the product code to search

    121        @return true if there is a product with the given code

    122     */

    123     private static boolean findProduct(Connection conn, String code)

    124        throws SQLException

    125     {

    126        PreparedStatement stat = conn.prepareStatement(

    127           "SELECT * FROM Product WHERE Product_Code = ?");

    128        stat.setString(1, code);

    129        ResultSet result = stat.executeQuery();

    130        boolean found = result.next();

    131        stat.close();

    132        return found;

    133     }

    134

    135     /**

    136        Adds a line item to the database

    137        @param conn the database connection

    138        @param id the invoice ID

    139        @param code the product code

    140        @param quantity the quantity to order

    141     */

    142     private static void addLineItem(Connection conn, int id,

    143        String code, int quantity) throws SQLException

    144     {

    145        PreparedStatement stat = conn.prepareStatement(

    146           "INSERT INTO LineItem VALUES (?, ?, ?)");

    147        stat.setInt(1, id);

    148        stat.setString(2, code);

    149        stat.setInt(3, quantity);

    150        stat.executeUpdate();

    151        stat.close();

    152     }

    153

    154     /**

    155        Lists all products in the database.

    156        @param conn the database connection

    157     */

    158     private static void listProducts(Connection conn)

    159        throws SQLException

    160     {

    161        Statement stat = conn.createStatement();

    162        ResultSet result = stat.executeQuery(

    163           "SELECT Product_Code, Description FROM Product");

    164        while (result.next())

    165        {

    166           String code = result.getString(1);

    167           String description = result.getString(2);

    168           System.out.println(code + " " + description);

    169        }

    170        stat.close();

    171     }

    172

    173     /**

    174        Gets a new ID for a table. This method should be called from

    175        inside a transaction that also creates the new row with this ID.

    176        The ID field should have name table_Number and type INTEGER.

    177        @param table the table name

    178        @return a new ID that has not yet been used.

    179     */

    180     private static int getNewId(Connection conn, String table)

    181        throws SQLException

    182     {

    183        Statement stat = conn.createStatement();

    184        ResultSet result = stat.executeQuery(

    185           "SELECT max(" + table + "_Number) FROM " + table);

    186        result.next();

    187        int max = result.getInt(1);

    188        stat.close();

    189        return max + 1;

    190     }

    191

    192     /**

    193        Shows an invoice.

    194        @param conn the database connection

    195        @param id the invoice ID

    196     */

    197     private static void showInvoice(Connection conn, int id)

    198        throws SQLException

    199     {

    200        PreparedStatement stat = conn.prepareStatement(

    201           "SELECT Customer.Name, Customer.Address, "

    202           + "Customer.City, Customer.State, Customer.Zip "

    203           + "FROM Customer, Invoice "

    204           + "WHERE Customer.Customer_Number = Invoice.Customer_Number "

    205           + "AND Invoice.Invoice_Number = ?");

    206        stat.setInt(1, id);

    207        ResultSet result = stat.executeQuery();

    208        result.next();

    209        System.out.println(result.getString(1));

    210        System.out.println(result.getString(2));

    211        System.out.println(result.getString(3).trim() + ", "

    212           + result.getString(4) + " " + result.getString(5));

    213        stat.close();

    214

    215        stat = conn.prepareStatement(

    216           "SELECT Product.Product_Code, Product.Description, LineItem.Quantity "

    217           + "FROM Product, LineItem "

    218           + "WHERE Product.Product_Code = LineItem.Product_Code "

    219           + "AND LineItem.Invoice_Number = ?");

    220        stat.setInt(1, id);

    221

    222        result = stat.executeQuery();

    223        while (result.next())

    224        {

    225           String code = result.getString(1);

    226           String description = result.getString(2).trim();

    227           int qty = result.getInt(3);

    228

    229           System.out.println(qty + " x " + code + " " + description);

    230        }

    231        stat.close();

    232     }

    233

    234     /**

    235        Prompts the user and reads a line from a scanner.

    236        @param in the scanner

    237        @param prompt the prompt

    238        @return the string that the user entered

    239     */

    240     private static String nextLine(Scanner in, String prompt)

    241     {

    242        System.out.print(prompt + ": ");

    243        return in.nextLine();

    244     }

    245

    246     /**

    247        Prompts the user and reads an integer from a scanner.

    248        @param in the scanner

    249        @param prompt the prompt

    250        @return the integer that the user entered

    251     */

    252     private static int nextInt(Scanner in, String prompt)

    253     {

    254        System.out.print(prompt + ": ");

    255        int result = in.nextInt();

    256        in.nextLine(); // Consume newline

    257        return result;

    258     }

    259  }